﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Net;
using System.Xml;
using DalatCooking.Core;
using DalatCooking.Core.Helper;
using System.IO;
using System.Text;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    public DataTable ReadDataFromExcel(string filePath)
    {
        DataTable table = new DataTable();
        // string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\hj.xlsx; Extended Properties=""Excel 8.0;HDR=Yes"";";
        // 2007
        // connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + "" + @";Extended Properties=""Excel 12.0 Xml;HDR=YES"";";
        string connectionString = "";

        if (Path.GetExtension(filePath) == ".xlsx")
        {
            connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + "" + @";Extended Properties=""Excel 12.0 Xml;HDR=YES"";";
        }
        else
        {
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;";
        }

        string CommandText = "select * from [Sheet1$]";

        System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(connectionString);
        System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(CommandText, myConnection);

        myConnection.Open();

        table.Load(myCommand.ExecuteReader(CommandBehavior.CloseConnection));

        myConnection.Close();

        return table;
    }
    void InsertDataBase(string filePath)
    {
        DataTable table = ReadDataFromExcel(filePath);
        string ConnectionString = @"Data Source=(LOCAL)\SQLEXPRESS; Initial Catalog=DalatCooking; User ID=sa; Password=sa";
        System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(ConnectionString);
        int i = 0;
        cnn.Open();
        foreach (DataRow row in table.Rows)
        {
            string CommandText = "insert into MeKiep (ID, Name, Age) values(@ID,@Name, @Age)";
            System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(CommandText, cnn);
            sqlCommand.Parameters.AddWithValue("@ID", row["ID"].ToString());
            sqlCommand.Parameters.AddWithValue("@Name", row["Name"].ToString());
            sqlCommand.Parameters.AddWithValue("@Age", row["Age"].ToString());
            sqlCommand.ExecuteNonQuery();
            i++;
        }
        cnn.Close();
    }
    protected void btnRead_Click(object sender, EventArgs e)
    {
        string dirUp = Server.MapPath("/Uploads");
        string fileName = Path.GetFileName(fileUpload.PostedFile.FileName);

        string filePath = dirUp + "\\" + fileName;

        fileUpload.SaveAs(filePath);
        lblMsg.Text = filePath;

        grid.DataSource = ReadDataFromExcel(filePath);
        grid.DataBind();

        // Insert xuong CSDL
        //  InsertDataBase(filePath);

        lblMsg.Text = "OK";
    }



    protected void btnGetData_Click(object sender, EventArgs e)
    {
        grid.DataSource = PersonManager.FindAll();
        grid.DataBind();
    }
    protected void btnExporExcel_Click(object sender, EventArgs e)
    {
        List<Person> list = PersonManager.FindAll();
        if (list.Count > 0)
        {
            string filename = "DownloadMobileNoExcel.xls";
            System.IO.StringWriter tw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
            DataGrid dgGrid = new DataGrid();
            dgGrid.DataSource = list;
            dgGrid.DataBind();
            hw.Write("<b>DEMO haha</b></br>Chào thế giới</br>");

            //Get the HTML for the control.
            dgGrid.RenderControl(hw);
            //Write the HTML back to the browser.
            //Response.ContentType = application/vnd.ms-excel;
            Response.ContentType = "application/vnd.ms-excel";
            Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
            this.EnableViewState = false;
            Response.Write(tw.ToString());
            Response.End();
        }
    }

    protected void btnExportToExcel_Click(object sender, EventArgs e)
    {
        DataTable tb1 = new DataTable();
        tb1 = DataProvider.IDataProvider.Instance.ExecuteDataset("Person_FindAll").Tables[0];

        string filename = "demo.xls";
        DataTable temp = new DataTable();

        temp.Columns.Add("<b>Mã học sinh </b>");
        temp.Columns.Add("<b>Tên học sinh</b>");
        temp.Columns.Add("<b>Mã lớp</b>");
        temp.Columns.Add("<b>Giới tính</b>");
        temp.Columns.Add("<b>Ngày sinh</b>");
        DataRow rowTemp;
        for (int i = 0; i < tb1.Rows.Count; i++)
        {
            rowTemp = temp.NewRow();
            // Ma hoc sinh
            rowTemp[0] = tb1.Rows[i][0].ToString();

            // Ma hoc sinh
            rowTemp[1] = tb1.Rows[i][1].ToString();

            // Ma hoc sinh
            rowTemp[2] = tb1.Rows[i][2].ToString();

            // Ma hoc sinh
            rowTemp[3] = tb1.Rows[i][3].ToString();
            // Ma hoc sinh
            rowTemp[4] = tb1.Rows[i][4].ToString();
            temp.Rows.Add(rowTemp);
        }

        System.IO.StringWriter tw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
        DataGrid dgGrid = new DataGrid();
        dgGrid.DataSource = temp;
        dgGrid.DataBind();
        // Tim hieu ve doi tuong HtmlTextWriter ....
        hw.Write("<b style=\"\">Danh sach sinh viên </b></br>");
        //Get the HTML for the control.
        dgGrid.RenderControl(hw);
        //Write the HTML back to the browser.
        //Response.ContentType = application/vnd.ms-excel;
        Response.ContentType = "application/vnd.ms-excel";
        Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
        this.EnableViewState = false;
        Response.Write(tw.ToString());
        Response.End();
    }

    private void ExportToExcel(string strFileName, DataGrid dg)
    {
        Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
        Response.ContentType = "application/excel";
        System.IO.StringWriter sw = new System.IO.StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        dg.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();
    }
}